#!/usr/bin/python
# -*- coding: UTF-8 -*-
from flask import Blueprint, request, jsonify, json
from flask import render_template
from flask import g
from .db import get_db
from flaskr.CommonUtils.DBUtils import *
from flaskr.CommonUtils.CommonTool import *

import psycopg2
from psycopg2.extras import execute_values
from typing import List


bp = Blueprint("compare", __name__)


@bp.route("/compare")
def compare():
    return render_template("compare/compare.html")


@bp.route('/getCompareData', methods=['GET', 'POST'])
def get_compare_data():
    sql_string_tmp = (
        "select * from compare_table "
        " order by 1"
    )
    return get_layui_json(sql_string_tmp)


@bp.route("/compareColumn", methods=['GET', 'POST'])
def compare_column():
    table_name = ""
    column_name = ""
    if request.method == "GET":
        table_name = request.args.get("table_name")
        column_name = request.values.get("column_name")
        g.table_name = table_name
        g.column_name = column_name

        print("table_name = " + str(table_name))
        print("column_name = " + str(column_name))
    return render_template("compare/compareColumn.html", posts=(table_name, column_name))


@bp.route('/getCompareColumnData', methods=['GET', 'POST'])
def get_compare_column_data():
    # global table_name_string, column_name_string
    tmp_string = ""
    table_name_string = ""
    column_name_string = ""

    if request.method == "GET":
        table_name_string = request.args.get("table_name")
        column_name_string = request.values.get("column_name")
        g.table_name = table_name_string
        g.column_name = column_name_string

        print("/getCompareColumnData : table_name = " + str(table_name_string))
        print("/getCompareColumnData : column_name = " + str(column_name_string))

    # data = json.loads(request.get_data(as_text=True))
    if table_name_string is None:
        table_name_string = ""
    if column_name_string is None:
        column_name_string = ""

    print("/getCompareColumnData : table_name = " + str(table_name_string))
    print("/getCompareColumnData : column_name = " + str(column_name_string))

    print("/getCompareColumnData : not table_name_string = " + str(not table_name_string))

    if table_name_string and len(table_name_string) != 0:
        tmp_string = tmp_string + " and t.table_name like '%" + table_name_string + "%'"
        print("/getCompareColumnData 2 1 : tmp_string = " + str(tmp_string))
    if column_name_string and len(column_name_string) != 0:
        tmp_string = tmp_string + " and t.column_name like '%" + column_name_string + "%'"
        print("/getCompareColumnData 2 2 : tmp_string = " + str(tmp_string))

    print("tmp_string = " + str(tmp_string))

    sql_string_tmp = (
            """
with gauss_plan_sit as (
    select *
      from compare_table
     where source = 'gauss_plan_sit'
     order by 1)
, pg_plan_uat as (
    select *
      from compare_table
     where source = 'pg_plan_uat'
     order by 1)
, all_tab_col_info as (
select schema
      ,table_name
      ,column_name
  from gauss_plan_sit
union
select schema
      ,table_name
      ,column_name
  from pg_plan_uat
order by 1,2,3)
select t.schema
      ,t.table_name
      ,t.column_name
      ,case when t1.column_name is not null then '√' else t1.column_name end as gauss_plan_sit
      ,case when t2.column_name is not null then '√' else t2.column_name end as pg_plan_uat
  from all_tab_col_info t
  left join gauss_plan_sit t1
    on t.table_name  = t1.table_name
   and t.column_name = t1.column_name
  left join pg_plan_uat t2
    on t.table_name  = t2.table_name
   and t.column_name = t2.column_name
 where 1=1
   """ + tmp_string + """
order by 1,2,3
        """
    )
    return get_layui_json(sql_string_tmp)


def get_layui_json(sql_string_i: str):
    """Show all the posts, most recent first."""
    # db = get_db()
    # data = db.execute(
    #     sql_string_i
    # ).fetchall()

    dBUtils = DBUtils()
    dBUtils.value["dbname"] = "tzqdb"
    dBUtils.value["username"] = "tzq"
    dBUtils.value["password"] = "1"
    dBUtils.value["host"] = "127.0.0.1"
    dBUtils.value["port"] = "5432"

    data = dBUtils.exec_sql_get_result_from_gauss_or_pg(sql_string_i)

    tmp = []
    for item in data:
        tmp.append(dict(item))
    # print("tmp = " + str(tmp))

    code = 0  # 表示成功
    message = '查询成功'  # 消息
    count = len(data)  # 数据总数，用于分页

    # 从请求中获取分页参数
    limit = int(request.args.get('limit', 10))  # 每页显示的数量
    page = int(request.args.get('page', 10))  # 每页显示的数量
    # print("limit = " + str(limit))
    # print("page = " + str(page))
    # 从数据库获取数据
    # users = tmp[offset:offset + limit]
    result_data = tmp[(page - 1) * limit:page * limit]
    # 返回Layui所需的格式
    return jsonify(code=code, message=message, count=count, data=result_data)


# 配置数据源
@bp.route("/configDatabaseSource", methods=['GET', 'POST'])
def config_database_source():
    connect_name = ""
    host = ""
    if request.method == "GET":
        connect_name = request.args.get("connect_name")
        host = request.values.get("host")
        g.table_name = connect_name
        g.column_name = host

        print("/configDatabaseSource : connect_name = " + str(connect_name))
        print("/configDatabaseSource : host = " + str(host))
    return render_template("compare/configDatabaseSource.html", posts=(connect_name, host))


# 配置数据源 - layui获取数据
@bp.route('/getConfigDatabaseSourceData', methods=['GET', 'POST'])
def get_config_database_source_data():
    # global table_name_string, column_name_string
    tmp_string = ""
    connect_name_string = ""
    host_string = ""

    if request.method == "GET":
        connect_name_string = request.args.get("connect_name")
        host_string = request.values.get("host")
        g.table_name = connect_name_string
        g.column_name = host_string

        print("/getConfigDatabaseSourceData : connect_name = " + str(connect_name_string))
        print("/getConfigDatabaseSourceData : host = " + str(host_string))

    # data = json.loads(request.get_data(as_text=True))
    if connect_name_string is None:
        connect_name_string = ""
    if host_string is None:
        host_string = ""

    if connect_name_string and len(connect_name_string) != 0:
        tmp_string = tmp_string + " and t.connect_name like '%" + connect_name_string + "%'"
        print("/getConfigDatabaseSourceData 2 1 : tmp_string = " + str(tmp_string))
    if host_string and len(host_string) != 0:
        tmp_string = tmp_string + " and t.host like '%" + host_string + "%'"
        print("/getConfigDatabaseSourceData 2 2 : tmp_string = " + str(tmp_string))

    print("tmp_string = " + str(tmp_string))

    sql_string_tmp = (
            """
select db_cfg_id as id, connect_name, database_type, host, port, dbname, username, password, created_by
      ,to_char(creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
      ,last_updated_by
      ,to_char(last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
      ,description 
  from tzq_db_cfg_t t
 where 1=1
   """ + tmp_string + """
order by last_update_date desc
        """
    )
    return get_layui_json(sql_string_tmp)


@bp.route("/addDatabaseSource")
def add_database_source():
    return render_template("compare/addDatabaseSource.html")


@bp.route("/addDatabaseSourceData", methods=['GET'])
def add_database_source_data():
    database_source_data_of_form_string = ""
    if request.method == "GET":
        # 获取通过AJAX提交的数据
        database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
        print("/addDatabaseSourceData : database_source_data_of_form_string = "
              + str(database_source_data_of_form_string))
    # database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
    # g.databaseSourceDataOfForm = database_source_data_of_form_string
    # 把数据插入到数据库
    # data_list_tmp = DBUtils.from_json_to_list(database_source_data_of_form_string)
    list_tmp = []
    str_tmp = eval(database_source_data_of_form_string)
    list_tmp.append(str_tmp)
    # 检查是否能够正常插入

    dbutils = DBUtils()
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"

    dbutils.common_insert_table_from_data_list_no_delete("tzq_db_cfg_t"
                                               , list_tmp)

    return "addDatabaseSourceData execute success"  # render_template("compare/addDatabaseSource.html")


@bp.route("/editDatabaseSource", methods=['GET', 'POST'])
def edit_database_source():
    # id_string = ""
    # data = ""
    tmp = []
    result_data_tmp = ""
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/editDatabaseSource : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"

        data = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select db_cfg_id, connect_name, database_type, host, port, dbname, username, password, created_by
              ,to_char(creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
              ,last_updated_by
              ,to_char(last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
              ,description 
          from tzq_db_cfg_t t
         where db_cfg_id = """ + id_string + """
        """)

        for item in data:
            tmp.append(dict(item))
        # print("tmp = " + str(tmp))

        # print("/editDatabaseSource : data = " + str(data))
        # result_data_tmp = jsonify(dict(data))
        # print("/editDatabaseSource : result_data_tmp = " + result_data_tmp)
    return render_template("compare/editDatabaseSource.html", data=tmp)


@bp.route("/editDatabaseSourceData", methods=['GET'])
def edit_database_source_data():
    database_source_data_of_form_string = ""
    if request.method == "GET":
        # 获取通过AJAX提交的数据
        database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
        print("/editDatabaseSourceData : database_source_data_of_form_string = "
              + str(database_source_data_of_form_string))
    # database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
    # g.databaseSourceDataOfForm = database_source_data_of_form_string
    list_tmp = []
    str_tmp = eval(database_source_data_of_form_string)
    list_tmp.append(str_tmp)
    print("list_tmp = " + str(list_tmp))
    print("list_tmp[0] = " + str(list_tmp[0]))
    print("list_tmp[0]['connect_name'] = " + str(list_tmp[0]['connect_name']))
    # 获取数据库连接
    dbutils = DBUtils()
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"
    sql_script_tmp = ("""
update tzq_db_cfg_t
   set connect_name = '""" + str(list_tmp[0]['connect_name']) + """'
      ,database_type = '""" + str(list_tmp[0]['database_type']) + """'
      ,host = '""" + str(list_tmp[0]['host']) + """'
      ,port = """ + str(list_tmp[0]['port']) + """
      ,dbname = '""" + str(list_tmp[0]['dbname']) + """'
      ,username = '""" + str(list_tmp[0]['username']) + """'
      ,password = '""" + str(list_tmp[0]['password']) + """'
      ,last_update_date = CURRENT_TIMESTAMP
      ,description = '""" + str(list_tmp[0]['description']) + """'
 where db_cfg_id = """ + str(list_tmp[0]['db_cfg_id']) + """
    """)
    print("/editDatabaseSourceData : sql_script_tmp = " + sql_script_tmp)
    dbutils.conn_db_exec_sql_from_dict(sql_script_tmp)
    return "editDatabaseSourceData execute success"  # render_template("compare/addDatabaseSource.html")


@bp.route("/deleteDatabaseSourceData", methods=['GET', 'POST'])
def delete_database_source_data():
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/deleteDatabaseSource : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"

        # 执行命令
        data = dbutils.conn_db_exec_sql_from_dict("""
        delete
          from tzq_db_cfg_t t
         where db_cfg_id = """ + id_string + """
        """)

        # print("/editDatabaseSource : data = " + str(data))
        # result_data_tmp = jsonify(dict(data))
        # print("/editDatabaseSource : result_data_tmp = " + result_data_tmp)
    return "deleteDatabaseSourceData execute success"


@bp.route("/detailDatabaseSource", methods=['GET', 'POST'])
def detail_database_source():
    # id_string = ""
    # data = ""
    tmp = []
    result_data_tmp = ""
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/detailDatabaseSource : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"

        data = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select db_cfg_id, connect_name, database_type, host, port, dbname, username, password, created_by
              ,to_char(creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
              ,last_updated_by
              ,to_char(last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
              ,description 
          from tzq_db_cfg_t t
         where db_cfg_id = """ + id_string + """
        """)

        for item in data:
            tmp.append(dict(item))
        # print("tmp = " + str(tmp))

        # print("/editDatabaseSource : data = " + str(data))
        # result_data_tmp = jsonify(dict(data))
        # print("/editDatabaseSource : result_data_tmp = " + result_data_tmp)
    return render_template("compare/detailDatabaseSource.html", data=tmp)


# 检查数据源是否能够连接成功
@bp.route("/checkDatabaseSourceLink", methods=['GET', 'POST'])
def check_database_source_link():
    result_tmp = None
    return_string = ""
    if request.method == "GET":
        # 获取通过AJAX提交的数据
        database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
        print("/checkDatabaseSourceLink : database_source_data_of_form_string = "
              + str(database_source_data_of_form_string))
        list_tmp = []
        str_tmp = eval(database_source_data_of_form_string)
        list_tmp.append(str_tmp)
        print("list_tmp = " + str(list_tmp))
        print("list_tmp[0] = " + str(list_tmp[0]))
        print("list_tmp[0]['connect_name'] = " + str(list_tmp[0]['connect_name']))

        dbutils_check = DBUtils()
        dbutils_check.value["database_type"] = str(list_tmp[0]['database_type'])
        dbutils_check.value["dbname"] = str(list_tmp[0]['dbname'])
        dbutils_check.value["username"] = str(list_tmp[0]['username'])
        dbutils_check.value["password"] = str(list_tmp[0]['password'])
        dbutils_check.value["host"] = str(list_tmp[0]['host'])
        dbutils_check.value["port"] = str(list_tmp[0]['port'])
        result_tmp = dbutils_check.check_database_connection()
        if result_tmp:
            return_string = "Successfully connected to database"
        else:
            return_string = "Failed to connect to database"
    return return_string


# 初始化数据库的表结构数据到比对系统的表中
@bp.route("/initDatabaseDataToCompareSystem", methods=['GET', 'POST'])
def init_database_data_to_compare_system():
    try:
        result_tmp = None
        return_string = ""
        if request.method == "GET":
            # 获取通过AJAX提交的数据
            database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
            print("/checkDatabaseSourceLink : database_source_data_of_form_string = "
                  + str(database_source_data_of_form_string))
            list_tmp = []
            str_tmp = eval(database_source_data_of_form_string)
            list_tmp.append(str_tmp)
            print("list_tmp = " + str(list_tmp))
            print("list_tmp[0] = " + str(list_tmp[0]))
            print("list_tmp[0]['connect_name'] = " + str(list_tmp[0]['connect_name']))

            dbutils_check = DBUtils()
            dbutils_check.value["connect_name"] = str(list_tmp[0]['connect_name'])
            dbutils_check.value["database_type"] = str(list_tmp[0]['database_type'])
            dbutils_check.value["dbname"] = str(list_tmp[0]['dbname'])
            dbutils_check.value["username"] = str(list_tmp[0]['username'])
            dbutils_check.value["password"] = str(list_tmp[0]['password'])
            dbutils_check.value["host"] = str(list_tmp[0]['host'])
            dbutils_check.value["port"] = str(list_tmp[0]['port'])
            result_tmp = dbutils_check.init_database_data()
            if result_tmp:
                return_string = "Successfully init_database_data"
            else:
                return_string = "Failed init_database_data"
    except Exception as e:
        print(f"Failed to init Database Data To Compare System : {e}")
        return f"Failed to init Database Data To Compare System : {e}"
    return return_string


# 配置比对任务
@bp.route("/configCompareTask", methods=['GET', 'POST'])
def config_compare_task():
    task_name = ""
    connect_name = ""
    if request.method == "GET":
        task_name = request.values.get("task_name")
        connect_name = request.args.get("connect_name")
        print("/configCompareTask : task_name = " + str(task_name))
        print("/configCompareTask : connect_name = " + str(connect_name))
    return render_template("compare/configCompareTask.html", posts=(task_name, connect_name))


# 配置比对任务 - layui获取数据
@bp.route('/getConfigCompareTaskData', methods=['GET', 'POST'])
def get_config_compare_task_data():
    tmp_string = ""
    task_name_string = ""
    connect_name_string = ""
    task_name_tmp_string = ""
    connect_name_tmp_string = ""
    if request.method == "GET":
        task_name_string = request.values.get("task_name")
        connect_name_string = request.args.get("connect_name")
        print("/getConfigCompareTaskData : task_name = " + str(task_name_string))
        print("/getConfigCompareTaskData : connect_name = " + str(connect_name_string))
    # data = json.loads(request.get_data(as_text=True))
    if task_name_string is None:
        task_name_string = ""
    if connect_name_string is None:
        connect_name_string = ""
    if task_name_string and len(task_name_string) != 0:
        task_name_tmp_string = task_name_tmp_string + " and task_name like '%" + task_name_string + "%'"
        print("/getConfigCompareTaskData 2 2 : task_name_tmp_string = " + str(task_name_tmp_string))
    if connect_name_string and len(connect_name_string) != 0:
        connect_name_tmp_string = connect_name_tmp_string + connect_name_string
        print("/getConfigCompareTaskData 2 1 : connect_name_tmp_string = " + str(connect_name_tmp_string))
    sql_string_tmp = (
            """
select * 
  from (select ARRAY[connect_name1, connect_name2, connect_name3,
                     connect_name4, connect_name5, connect_name6, connect_name7] as a1
              ,t.* 
          from (select t.task_id as id
                      ,t.task_name
                      ,t.db_cfg_1_id ,a1.connect_name as connect_name1
                      ,t.db_cfg_2_id ,a2.connect_name as connect_name2
                      ,t.db_cfg_3_id ,a3.connect_name as connect_name3
                      ,t.db_cfg_4_id ,a4.connect_name as connect_name4
                      ,t.db_cfg_5_id ,a5.connect_name as connect_name5
                      ,t.db_cfg_6_id ,a6.connect_name as connect_name6
                      ,t.db_cfg_7_id ,a7.connect_name as connect_name7
                      ,t.created_by
                      ,to_char(t.creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
                      ,t.last_updated_by
                      ,to_char(t.last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
                      ,t.description
                  from compare_task_t t
                  left join tzq_db_cfg_t a1 on a1.db_cfg_id = t.db_cfg_1_id
                  left join tzq_db_cfg_t a2 on a2.db_cfg_id = t.db_cfg_2_id
                  left join tzq_db_cfg_t a3 on a3.db_cfg_id = t.db_cfg_3_id
                  left join tzq_db_cfg_t a4 on a4.db_cfg_id = t.db_cfg_4_id
                  left join tzq_db_cfg_t a5 on a5.db_cfg_id = t.db_cfg_5_id
                  left join tzq_db_cfg_t a6 on a6.db_cfg_id = t.db_cfg_6_id
                  left join tzq_db_cfg_t a7 on a7.db_cfg_id = t.db_cfg_7_id) t
       ) q 
 where 1=1
   """ + task_name_tmp_string + """
   and EXISTS (
      SELECT 1
        FROM generate_subscripts(q.a1, 1) AS t(i)
       WHERE q.a1[t.i]  ~* concat('^.*', '""" + connect_name_tmp_string + """', '.*$'))
order by last_update_date desc
        """
    )
    return get_layui_json(sql_string_tmp)


@bp.route("/addCompareTask")
def add_compare_task():
    sql_string_tmp = (
        """
select * 
  from tzq_db_cfg_t
 order by last_update_date desc
        """
    )
    dbutils = DBUtils()
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"
    data = dbutils.exec_sql_get_result_from_gauss_or_pg(sql_string_tmp)
    tmp = []
    for item in data:
        tmp.append(dict(item))
    # print("tmp = " + str(tmp))
    return render_template("compare/addCompareTask.html", dbsources=tmp)


@bp.route("/addCompareTaskData", methods=['GET'])
def add_compare_task_data():
    database_source_data_of_form_string = ""
    if request.method == "GET":
        # 获取通过AJAX提交的数据
        database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
        print("/addCompareTaskData : database_source_data_of_form_string = "
              + str(database_source_data_of_form_string))
    list_tmp = []
    str_tmp = eval(database_source_data_of_form_string)
    print("type(str_tmp)=" + str(type(str_tmp)))
    str_tmp = CommonTool.convert_values(str_tmp)
    print("str_tmp=" + str(str_tmp))
    list_tmp.append(str_tmp)
    # 检查是否能够正常插入
    dbutils = DBUtils()
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"

    save_data_to_pg(list_tmp
                    , "compare_task_t", "tzqdb"
                    , "tzq"
                    , "1", "127.0.0.1", "5432" )

    # dbutils.common_insert_table_from_data_list_no_delete("compare_task_t"
    #                                            , list_tmp)
    return "addCompareTaskData execute success"  # render_template("compare/addDatabaseSource.html")


@bp.route("/detailCompareTask", methods=['GET', 'POST'])
def detail_compare_task():
    # id_string = ""
    # data = ""
    task_name = ""
    id_string = ""
    tmp = []
    tmp2 = []
    source_column_info = ""
    result_data_tmp = ""
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/detailCompareTask : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"
        print("str(id_string)=" + str(id_string))

        data = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select * from get_table_by_task_id(""" + str(id_string) + """)
        """)

        task_name = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select task_name from compare_task_t where task_id=""" + str(id_string) + """
        """)

        print("data=" + str(data))
        for item in data:
            tmp.append(dict(item))
        # print("tmp = " + str(tmp))

        source_column_info = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select array_agg(distinct source) as arr,array_length(array_agg(distinct source), 1) as len
          from compare_task_info_v where task_id = """ + str(id_string) + """
        """)
        print("source_column_info = " + str(source_column_info))

        # print("/editDatabaseSource : data = " + str(data))
        # result_data_tmp = jsonify(dict(data))
        # print("/editDatabaseSource : result_data_tmp = " + result_data_tmp)
    return render_template("compare/detailCompareTask.html"
                           , source_column_info=source_column_info
                           , data=tmp, id=id_string, task_name=task_name)


@bp.route("/detailCompareTaskData", methods=['GET'])
def detail_compare_task_data():
    sql_string_tmp = ""
    table_name_string = ""
    column_name_string = ""
    table_name_tmp_string = ""
    column_name_tmp_string = ""
    check_result_tmp_string = ""
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/detailCompareTaskData : id_string = " + str(id_string))

        table_name_string = request.values.get("table_name")
        column_name_string = request.args.get("column_name")
        print("/detailCompareTaskData : table_name = " + str(table_name_string))
        print("/detailCompareTaskData : column_name = " + str(column_name_string))

        check_result_string = request.args.get("check_result")
        print("/detailCompareTaskData : check_result_string = " + str(check_result_string))

        if table_name_string is None:
            table_name_string = ""
        if column_name_string is None:
            column_name_string = ""
        if table_name_string and len(table_name_string) != 0:
            table_name_tmp_string = table_name_tmp_string + " and table_name like '%" + table_name_string + "%'"
            print("/detailCompareTaskData 2 1 : table_name_tmp_string = " + str(table_name_tmp_string))
        if column_name_string and len(column_name_string) != 0:
            column_name_tmp_string = column_name_tmp_string + " and column_name like '%" + column_name_string + "%'"
            print("/detailCompareTaskData 2 2 : column_name_tmp_string = " + str(column_name_tmp_string))

        if check_result_string is None:
            check_result_string = ""
        if check_result_string and len(check_result_string) != 0:
            check_result_tmp_string = check_result_tmp_string + " and total_check_result = '" + check_result_string + "'"

        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"
        print("str(id_string)=" + str(id_string))
        sql_string_tmp = ("""
select * from get_table_info_by_task_id_f(""" + str(id_string) + """)
 where 1=1
               """ + str(table_name_tmp_string) + """
               """ + str(column_name_tmp_string) + """
               """ + str(check_result_tmp_string) + """
            """)
        # data = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        #         select * from get_table_by_task_id(""" + str(id_string) + """)
        #         """)
        # print("data=" + str(data))
        # for item in data:
        #     tmp.append(dict(item))
    return get_layui_json(sql_string_tmp)


@bp.route("/deleteCompareTaskData", methods=['GET', 'POST'])
def delete_compare_task_data():
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/deleteCompareTaskData : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"

        # 执行命令
        data = dbutils.conn_db_exec_sql_from_dict("""
        delete
          from compare_task_t t
         where task_id = """ + id_string + """
        """)

    return "deleteCompareTaskData execute success"


@bp.route("/editCompareTask", methods=['GET', 'POST'])
def edit_compare_task():
    tmp = []
    tmp2 = []
    if request.method == "GET":
        id_string = request.args.get("id")
        print("/editCompareTask : id_string = " + str(id_string))
        dbutils = DBUtils()
        dbutils.value["dbname"] = "tzqdb"
        dbutils.value["username"] = "tzq"
        dbutils.value["password"] = "1"
        dbutils.value["host"] = "127.0.0.1"
        dbutils.value["port"] = "5432"
        data = dbutils.exec_sql_get_result_from_gauss_or_pg("""
        select t.task_id
              ,t.task_name
              ,t.db_cfg_1_id ,a1.connect_name as connect_name1
              ,t.db_cfg_2_id ,a2.connect_name as connect_name2
              ,t.db_cfg_3_id ,a3.connect_name as connect_name3
              ,t.db_cfg_4_id ,a4.connect_name as connect_name4
              ,t.db_cfg_5_id ,a5.connect_name as connect_name5
              ,t.db_cfg_6_id ,a6.connect_name as connect_name6
              ,t.db_cfg_7_id ,a7.connect_name as connect_name7
              ,t.created_by
              ,to_char(t.creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
              ,t.last_updated_by
              ,to_char(t.last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
              ,t.description
          from compare_task_t t
          left join tzq_db_cfg_t a1 on a1.db_cfg_id = t.db_cfg_1_id
          left join tzq_db_cfg_t a2 on a2.db_cfg_id = t.db_cfg_2_id
          left join tzq_db_cfg_t a3 on a3.db_cfg_id = t.db_cfg_3_id
          left join tzq_db_cfg_t a4 on a4.db_cfg_id = t.db_cfg_4_id
          left join tzq_db_cfg_t a5 on a5.db_cfg_id = t.db_cfg_5_id
          left join tzq_db_cfg_t a6 on a6.db_cfg_id = t.db_cfg_6_id
          left join tzq_db_cfg_t a7 on a7.db_cfg_id = t.db_cfg_7_id
         where t.task_id = """ + id_string + """
        """)
        for item in data:
            tmp.append(dict(item))

        sql_string_tmp = (
            """
        select * 
          from tzq_db_cfg_t
         order by last_update_date desc
                """
            )
        dbutils2 = DBUtils()
        dbutils2.value["dbname"] = "tzqdb"
        dbutils2.value["username"] = "tzq"
        dbutils2.value["password"] = "1"
        dbutils2.value["host"] = "127.0.0.1"
        dbutils2.value["port"] = "5432"
        data2 = dbutils2.exec_sql_get_result_from_gauss_or_pg(sql_string_tmp)
        tmp2 = []
        for item2 in data2:
            tmp2.append(dict(item2))
        # print("tmp = " + str(tmp))

    return render_template("compare/editCompareTask.html", dbsources=tmp2, data=tmp)


@bp.route("/editCompareTaskData", methods=['GET'])
def edit_compare_task_data():
    database_source_data_of_form_string = ""
    if request.method == "GET":
        # 获取通过AJAX提交的数据
        database_source_data_of_form_string = request.args.get("databaseSourceDataOfForm")
        print("/editCompareTaskData : database_source_data_of_form_string = "
              + str(database_source_data_of_form_string))
    list_tmp = []
    str_tmp = eval(database_source_data_of_form_string)
    list_tmp.append(str_tmp)
    print("list_tmp = " + str(list_tmp))
    print("list_tmp[0] = " + str(list_tmp[0]))
    print("list_tmp[0]['task_name'] = " + str(list_tmp[0]['task_name']))
    # 获取数据库连接
    dbutils = DBUtils()
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"

    db_cfg_3_id_tmp_string = str(list_tmp[0]['db_cfg_3_id'])
    if db_cfg_3_id_tmp_string is None:
        db_cfg_3_id_tmp_string = ""
    if len(db_cfg_3_id_tmp_string) == 0:
        db_cfg_3_id_tmp_string = "null"
    print("db_cfg_3_id_tmp_string=" + str(db_cfg_3_id_tmp_string))

    db_cfg_4_id_tmp_string = str(list_tmp[0]['db_cfg_4_id'])
    if db_cfg_4_id_tmp_string is None:
        db_cfg_4_id_tmp_string = ""
    if len(db_cfg_4_id_tmp_string) == 0:
        db_cfg_4_id_tmp_string = "null"

    db_cfg_5_id_tmp_string = str(list_tmp[0]['db_cfg_5_id'])
    if db_cfg_5_id_tmp_string is None:
        db_cfg_5_id_tmp_string = ""
    if len(db_cfg_5_id_tmp_string) == 0:
        db_cfg_5_id_tmp_string = "null"

    db_cfg_6_id_tmp_string = str(list_tmp[0]['db_cfg_6_id'])
    if db_cfg_6_id_tmp_string is None:
        db_cfg_6_id_tmp_string = ""
    if len(db_cfg_6_id_tmp_string) == 0:
        db_cfg_6_id_tmp_string = "null"

    db_cfg_7_id_tmp_string = str(list_tmp[0]['db_cfg_7_id'])
    if db_cfg_7_id_tmp_string is None:
        db_cfg_7_id_tmp_string = ""
    if len(db_cfg_7_id_tmp_string) == 0:
        db_cfg_7_id_tmp_string = "null"

    sql_script_tmp = ("""
update compare_task_t
   set task_name   = '""" + str(list_tmp[0]['task_name']) + """'
      ,db_cfg_1_id = """ + str(list_tmp[0]['db_cfg_1_id']) + """
      ,db_cfg_2_id = """ + str(list_tmp[0]['db_cfg_2_id']) + """
      ,db_cfg_3_id = """ + str(db_cfg_3_id_tmp_string) + """
      ,db_cfg_4_id = """ + str(db_cfg_4_id_tmp_string) + """
      ,db_cfg_5_id = """ + str(db_cfg_5_id_tmp_string) + """
      ,db_cfg_6_id = """ + str(db_cfg_6_id_tmp_string) + """
      ,db_cfg_7_id = """ + str(db_cfg_7_id_tmp_string) + """
      ,description = '""" + str(list_tmp[0]['description']) + """'
      ,last_update_date = now()
 where task_id = """ + str(list_tmp[0]['task_id']) + """
    """)
    print("/editDatabaseSourceData : sql_script_tmp = " + sql_script_tmp)
    dbutils.conn_db_exec_sql_from_dict(sql_script_tmp)
    return "editCompareTaskData execute success"  # render_template("compare/addDatabaseSource.html")


def save_data_to_pg(data_list: List[dict], table_name: str, dbname: str, user: str, password: str, host: str, port: int)\
        -> None:
    """
    将字典列表数据存储到 PostgreSQL 数据库中

    Args:
        data_list (List[dict]): 包含字典数据的列表
        table_name (str): 要插入数据的表名
        dbname (str): 数据库名称
        user (str): 数据库用户名
        password (str): 数据库密码
        host (str): 数据库主机
        port (int): 数据库端口

    Returns:
        None
    """
    # 连接数据库
    conn = psycopg2.connect(
        dbname=dbname,
        user=user,
        password=password,
        host=host,
        port=port
    )
    # 创建游标
    cur = conn.cursor()
    try:
        # 获取字段名列表
        fields = list(data_list[0].keys())
        # 构造 SQL 语句
        sql = "INSERT INTO {} ({}) VALUES %s".format(
            table_name,
            ", ".join(fields)
        )
        # 获取数据值列表
        values = [[data[field] for field in fields] for data in data_list]
        # 执行 SQL 语句批量插入数据
        execute_values(cur, sql, values)
        # 提交更改
        conn.commit()
        print("成功插入{}条数据".format(len(data_list)))
    except Exception as e:
        # 发生异常时回滚
        conn.rollback()
        print("插入数据时发生异常：", e)
    finally:
        # 关闭游标和连接
        cur.close()
        conn.close()




